package com.zlj.jdbc.utils;

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;

/**
 * 关于查询操作，在得到resultSet后利用getMetaData()方法得到表的结构信息：
 * 如：getColumnCount()方法得到一种有多少列;
 *    String cols_name = metaData.getColumnName(i + 1);得到每个列的属性名称,比如：id username.....
 *    Object cols_value = resultSet.getObject(cols_name);取出每个列的属性对应的值，放到Map或者List<Map>中
 *    
 * @author bashen
 *
 */
public class JdbcUtils {
	private static Properties properties = null;
	private static Connection conn = null;
	private static PreparedStatement pstm = null;
	private static ResultSet resultSet = null;

	private JdbcUtils() {
		init();
	}

	private void init() {
		try {
			InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
			properties = new Properties();
			properties.load(in);
			Class.forName(properties.getProperty("dirverClassName"));
		} catch (ClassNotFoundException | IOException e) {
			e.printStackTrace();
		}
	}

	private static class LazyLoader {
		private static JdbcUtils INSTANCE = new JdbcUtils();
	}

	public static JdbcUtils getInstance() {
		return LazyLoader.INSTANCE;
	}

	/*
	 * 获取数据库连接
	 */
	public Connection getConnection() {
		if (conn == null) {
			try {
				conn = DriverManager.getConnection(properties.getProperty("jdbcUrl"), properties.getProperty("username"), properties.getProperty("password"));
				return conn;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		} 
		return conn;
	}
	
	/**
	 * 设置参数
	 */
	public void initParams(PreparedStatement pstm, Object... params) {
		for (int i = 0; i < params.length; i++) {
			try {
				pstm.setObject(i + 1, params[i]);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	/*
	 * 执行增删改操作
	 */
	public int update(String sql, Object... params) throws SQLException {
		pstm = getConnection().prepareStatement(sql);
		initParams(pstm, params);
		int update = pstm.executeUpdate();
		return update;
	}
	
	/**
	 * 执行单笔查询操作
	 * @throws SQLException 
	 */
	public Map<String, Object> findSingleResult(String sql, Object... params) throws SQLException {
		Map<String, Object> map = new HashMap<>(); 
		pstm = getConnection().prepareStatement(sql);
		initParams(pstm, params);
		resultSet = pstm.executeQuery();  //返回单笔查询结果
		ResultSetMetaData metaData = resultSet.getMetaData();  //获取结果集对象的列的属性
		int columnCount = metaData.getColumnCount();   //获取列的个数
		while(resultSet.next()) {
			for (int i = 0; i < columnCount; i++) {
				String columnName = metaData.getColumnName(i + 1);
				Object columnValue = resultSet.getObject(columnName);
				if(columnValue == null) {
					columnValue = "";
				}
				map.put(columnName, columnValue);
			}
		}
		return map;
	}

	/*
	 * 执行多笔查询操作
	 */
	public List<Map<String, Object>> findMoreResult(String sql, Object... params) throws SQLException {
		List<Map<String, Object>> list = new ArrayList<>();
		pstm = getConnection().prepareStatement(sql);
		initParams(pstm, params);
		resultSet = pstm.executeQuery();
		ResultSetMetaData metaData = resultSet.getMetaData();
		int columnCount = metaData.getColumnCount();
		while(resultSet.next()) {
			Map<String, Object> map = new HashMap<>();
			for (int i = 0; i < columnCount; i++) {
				String columnName = metaData.getColumnName(i + 1);
				Object columnValue = resultSet.getObject(columnName);
				if(columnValue == null) {
					columnValue = "";
				}
				map.put(columnName, columnValue);
			}
			list.add(map);
		}
		return list;
	}
	
	/*
	 * 通过反射机制查询单条记录
	 */
	public <T> T findSingleRefResult(String sql, Class<T> cls, Object... params) throws SQLException, InstantiationException, IllegalAccessException, NoSuchFieldException, SecurityException {
		T newInstance = null;
		pstm = getConnection().prepareStatement(sql);
		initParams(pstm, params);
		resultSet = pstm.executeQuery();
		ResultSetMetaData metaData = resultSet.getMetaData();
		int columnCount = metaData.getColumnCount();
		while(resultSet.next()) {
			newInstance = cls.newInstance();
			for (int i = 0; i < columnCount; i++) {
				String name = metaData.getColumnName(i + 1);
				Object value = resultSet.getObject(name);
				if(value == null) {
					value = "";
				}
				Field field = cls.getDeclaredField(name);
				field.setAccessible(true);  //打开JavaBean的访问权限
				field.set(newInstance, value);
			}
		}
		return newInstance;
	}
	
	/*
	 * 通过反射机制查询多条记录
	 */
	public <T> List<T> findMoreRefResult(String sql, Class<T> cls, Object... params) throws SQLException, InstantiationException, IllegalAccessException, NoSuchFieldException, SecurityException {
		List<T> list = new ArrayList<>();
		pstm = getConnection().prepareStatement(sql);
		initParams(pstm, params);
		resultSet = pstm.executeQuery();
		ResultSetMetaData metaData = resultSet.getMetaData();
		int columnCount = metaData.getColumnCount();
		while(resultSet.next()) {
			T newInstance = cls.newInstance();
			for (int i = 0; i < columnCount; i++) {
				String name = metaData.getColumnName(i + 1);
				Object value = resultSet.getObject(name);
				if(value == null) {
					value = "";
				}
				Field field = cls.getDeclaredField(name);
				field.setAccessible(true);
				field.set(newInstance, value);
			}
			list.add(newInstance);
		}
		return list;
	}
	 
	/*
	 * 释放数据库连接
	 */
	public void releaseConnection() {
		try {
			if (resultSet != null) {
				resultSet.close();
			}

			if (pstm != null) {
				pstm.close();
			}

			if (conn != null) {
				conn.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}
